*************************************************************
* This code generates an indicator for whether a person		*
* has been involved in a property trade during each year.	*
* To be used when creating family dataset.					*
* 															*
* Input: ejer												*
*		 bef												*
*															*
* Output: famejendomshandler								*
*															*
*************************************************************;

dm 'cle log';

proc datasets lib=work kill memtype=data;
run;

libname raw "E:\ProjektDB\706172\DST_rawdata_706172";
libname raw1 "E:\ProjektDB\706172\Workdata\706172\Datas�t\Hj�lpefiler";
libname samle "E:\ProjektDB\706172\Workdata\706172\Datas�t";
libname databib "E:/ProjektDB/706172/Workdata/706172/Datas�t";

%let startaar=2003;
%let slutaar=2017;
%let slutaar_1 = &slutaar + 1;

* Indicatios are generated for property sales.
* Notice that in 2006 there is a reform changing the municipalities in Denmark.;

%macro sorter;
* Sorting the dataset ejer on pnr, komnr and ejdnr. Deleting duplicates;
%do i=&startaar %to &slutaar_1;
%if &i <2017 %then %do;
proc sort data=raw.ejer&i out=ejer&i;
by pnr komnr ejdnr;
run;
%end;

%if &i>=2017 %then %do;
proc sort data=raw.ejer&i out=ejer&i;
by pnr kom ejendomsnummer;
run;
data ejer&i; set ejer&i;
rename ejendomsnummer = ejdnr ;
komnr=KOM * 1 ;
run;
%end;

data ejer&i;
set ejer&i;
by pnr komnr ejdnr;
if first.ejdnr=1;
run;

%end;

* Sorting dataset bef on pnr;
%do i=&startaar %to &slutaar_1;
proc sort data=raw.bef&i (keep=pnr familie_id) out=work.bef&i;
by pnr;
run;
%end;

%mend sorter;
%sorter;

* For every personal identifier (pnr) we list the amount of unique properties that the person owns in the year;
* That gives one observation per person per property;
* Only people who owns a property is included in ejendomme&i;

%macro liste;
%do i=&startaar %to &slutaar_1;
data ejendomme&i;
merge bef&i (in=a keep=pnr)
      ejer&i (keep= komnr ejdnr pnr ejerstart_dto_txt ejerpct)
;
by pnr;
if a=1;
if ejdnr ne "";
run; 

%if &i <= 2013 %then %do;
data ejendomme&i; set ejendomme&i;
ejerstartdto = input(ejerstart_dto_txt,yymmdd.);
drop ejerstart_dto_txt;
format ejerstartdto ddmmyy.;
run;
%end;
%if &i >2013 and &i <2017 %then %do;
data ejendomme&i; set ejendomme&i;
rename ejerstart_dto_txt = ejerstartdto;
format ejerstartdto ddmmyy.;
run;
%end;
%if &i >= 2017 %then %do;
data ejendomme&i; set ejendomme&i;
ejerstartdto = input(ejerstart_dto_txt,yymmdd.);
drop ejerstart_dto_txt;
format ejerstartdto ddmmyy.;
run;
%end;
%end;
%mend liste;
%liste;

* The information on people's properties primo year t is now combined with the same information the year t+1;
* The stock of properties primo year t+1 corresponds to the stock of properties ultimo year t;

* If a person owns a property in the beginning of a year, and not owns the same property in the end of the year, the property is defined as sold during that year.
  If a person owns a property in the end of a year, and not owns the same property in the beginning of the year, the property is defined as bought during that year.;

%macro ejendomsskift;
%do i=&startaar %to &slutaar;
%let j=%eval(&i+1);
%if &i ne 2006 %then %do;

data ejendomsskift&i;
merge ejendomme&i (in=a rename=(ejerpct=ejerpct_primo  ejerstartdto=ejerstartdto_primo))
      ejendomme&j (in=b rename=(ejerpct=ejerpct_ultimo ejerstartdto=ejerstartdto_ultimo))
;
by pnr komnr ejdnr;

if year(ejerstartdto_ultimo)>=&i then koebt_ejerstartdto=1; else koebt_ejerstartdto=0;

if a=1 and b=0 then solgt=1; else solgt=0;
if a=0 and b=1 then koebt=1; else koebt=0;
if koebt=1 or solgt=1 then ejerskift=1; else ejerskift=0;

label ejerpct_primo        = "Owner share, primo"
      ejerstartdto_primo   = "Owner start date, primo"
      ejerpct_ultimo       = "Owner share, ultimo"
      ejerstartdto_ultimo  = "Owner start date, ultimo"
;

run;
%end;
%end;
%mend ejendomsskift;
%ejendomsskift;

* Adjusting the population of individuals ultimo year;
%macro pop;
%do i=&startaar %to &slutaar;
%let j=%eval(&i+1);
%if &i ne 2006 %then %do;
data ejendomsskift_pop&i;
merge bef&j (in=a keep=pnr)
	  bef&i (in=b keep=pnr familie_id)
      ejendomsskift&i (in=c)
;
by pnr;
if a;                                                        * Conditioned on being in the population ultimo year;
if b=0 then do; koebt=.; ejerskift=.; end;                   * If a person does not exists in the population primo year, the indicator for buy is set to missing;
if b=1 and c=0 then do; koebt=0; solgt=0; ejerskift=0; end;  * If a person does not exists in ejendomsskift&i, the person does not own a property neither in the beginning 
															   nor in the end of the year. The person has not been involved in a property sale.;
run;
%end;
%end;
%mend pop;
%pop;

%macro ejendsk;
%do i=&startaar %to &slutaar;
%if &i=&startaar %then %do;
data ejendomsskift; set ejendomsskift_pop&startaar;
aar = &startaar;
run;
%end;
%if &i>&startaar and &i ne 2006 %then %do;
data ejendomsskift; 
set ejendomsskift (in=a)
	ejendomsskift_pop&i (in=b)
;
if b=1 then aar=&i;
run;
%end;
%end;
%mend ejendsk;
%ejendsk;

proc sort data=ejendomsskift;
by aar pnr komnr ejdnr;
run;

proc tabulate data=ejendomsskift;
class aar solgt;
table aar, solgt*rowpctn;
run;

proc tabulate data=ejendomsskift;
class aar koebt;
table aar, koebt*rowpctn;
run;

proc tabulate data=ejendomsskift;
class aar ejerskift;
table aar, ejerskift*rowpctn;
run;


proc means data=ejendomsskift noprint;
var koebt solgt ejerskift;
by aar pnr;
output out=ejendomshandler_pnr sum(koebt solgt ejerskift)=koebt solgt ejerskift;
run;


proc sort data=ejendomsskift out=ejendomsskift_famsort;
by aar familie_id komnr ejdnr pnr;
run;

proc means data=ejendomsskift_famsort noprint;
var koebt solgt ejerskift;
by aar familie_id;
output out=famejendomshandler sum(koebt solgt ejerskift)=koebt solgt ejerskift;
run;

data samle.famejendomshandler;
set famejendomshandler (keep=aar familie_id koebt solgt ejerskift)
raw1.ejdhandler06;
if solgt gt 0 then solgt=1; 
if koebt gt 0 then koebt=1;
if ejerskift gt 0 then ejerskift=1;
run;

proc sort data=samle.famejendomshandler;
by aar familie_id;
run;

proc tabulate data=samle.famejendomshandler;
class aar ejerskift;
table aar, ejerskift*(n rowpctn);
run;
